﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Net;
using System.IO;
using WF_DataAccess;
using WF_Business;

namespace Business.FlowOperation
{
    /// <summary>
    /// 功能描述：附件处理
    /// </summary>
    public class AttatchmentHelper
    {
        /// <summary>
        /// 查询当前业务的附件材料列表
        /// </summary>
        /// <param name="iid">当前业务IID</param>
        /// <returns>返回查询结果</returns>
        public DataTable QueryMenuList(long iid)
        {
            string strSql = @"select t.id,t.file_name,t.format_require,t.remark,case t.require when 1 then '否' when 0 then '是' end require,
(select count(iid) from st_dynamic_resource where iid=t.iid and path like substr(t.file_name,0,INSTR(path,'/'))||'/%') num 
from xt_accessories t where t.iid='" + iid+"' order by file_no";
            try
            {
                IDataAccess ida = SysParams.OAConnection();
                DataTable dt = new DataTable();
                ida.RunSql(strSql, out dt);
                return dt;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 查询当前材料下的附件列表
        /// </summary>
        /// <param name="iid">当前业务IID</param>
        /// <param name="path">当前材料名</param>
        /// <returns>返回查询结果</returns>
        public DataTable QueryAttach(long iid, string path)
        {
            IDataAccess ida = SysParams.OAConnection();
            //string strSql = "select substr(path,instr(path,'/')+1) filename,a.ext_name,t.path,get_moftppath(a.ftp_path) ftp_path, get_moftppath(substr(ftp_path, instr(ftp_path, '@') + 1)) ftppath from st_dynamic_resource t,st_attachment a where t.res_value=a.aid and t.type=2 and t.iid=" + iid + " and t.path like '" + path + "%'";

            string strSql = string.Format("select substr(path,instr(path,'/')+1) filename,a.ext_name,t.path,a.data from st_dynamic_resource t,st_attachment a where t.res_value=a.aid and t.type=2 and t.iid='{0}'",iid);

            if (!string.IsNullOrEmpty(path))
            {
                strSql += string.Format(" and t.path like '{0}%' ", path);
            }

            try
            {
                DataTable dt = new DataTable();
                ida.RunSql(strSql, out dt);

                return dt;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 删除所选材料
        /// </summary>
        /// <param name="path">需要删除的材料名称</param>
        /// <param name="iid">业务ID</param>
        /// <returns>返回提示</returns>
        public void DelMenu(string path, long iid, string id)
        {
            IDataAccess tran = SysParams.OAConnection(true);
            try
            {
                string strSql = "delete XT_ACCESSORIES where id='" + id + "'";
                tran.RunSql(strSql);
                strSql = "delete st_dynamic_resource where iid=" + iid + " and type=2 and path like '" + path + "/%'";
                tran.RunSql(strSql);
                strSql = "delete st_attachment where aid in (select res_value from st_dynamic_resource where type=2 and iid=" + iid + " and path like '" + path + "/%')";
                tran.RunSql(strSql);
                tran.Close(true);
            }
            catch
            {
                tran.Close(false);
                throw;
            }
        }

        #region 附件上传时数据库的操作

        /// <summary>
        /// 上传附件
        /// </summary>
        /// <param name="iid">业务id</param>
        /// <param name="path">st_dynamic_resource表中path的值</param>
        /// <param name="userid">当前上传附件的用户id</param>
        /// <param name="ftpPath">st_attachment表中附件的实际存放位置</param>
        /// <param name="extname">附件的后缀名</param>
        public void AddAttachment(long iid, string path, string userid, string ftpPath, string extname)
        {
            string resvalue = Guid.NewGuid().ToString();
            IDataAccess tran = SysParams.OAConnection(true);
            string strsql = "insert into st_dynamic_resource(iid,path,res_value,userid,type) values(" + iid + ",'" + path + "','" + resvalue + "','" + userid + "',2)";
            try
            {
                tran.RunSql(strsql);
                strsql = "insert into st_attachment(aid,magic_number,ext_name,ftp_path) values('" + resvalue + "',2,'" + extname + "','" + ftpPath + "')";
                tran.RunSql(strsql);
                tran.Close(true);                
            }
            catch
            {
                tran.Close(false);
                throw;
            }           
        }        
        #endregion

        #region 删除附件时的操作


        /// <summary>
        /// 删除所选附件
        /// </summary>
        /// <param name="name">附件名称</param>
        /// <param name="iid">业务ID</param>        
        public void DelAttach(string name, long iid)
        {
            IDataAccess tran = SysParams.OAConnection(true);
            string strSql = "delete st_attachment where aid in (select res_value from st_dynamic_resource where type=2 and iid=" + iid + " and path = '" + name + "')";
            try
            {
                tran.RunSql(strSql);
                strSql = "delete st_dynamic_resource where iid=" + iid + " and type=2 and path = '" + name + "'";
                tran.RunSql(strSql);
                tran.Close(true);
            }
            catch
            {
                tran.Close(false);
                throw;
            }
        }

        #endregion

      
        /// <summary>
        /// 查询st_dynamic_resource表中有满足条件的记录
        /// </summary>
        /// <param name="iid">业务id</param>
        /// <param name="path">附件在st_dynamic_resource表中path字段的值</param>
        /// <returns></returns>
        public DataTable QueryAttahcment(long iid, string path)
        {
            IDataAccess ida = SysParams.OAConnection();
            string strSql = "select t.iid,t.path from st_dynamic_resource t where t.iid=" + iid + " and t.path ='" + path + "'";
            try
            {
                DataTable dt = new DataTable();
                ida.RunSql(strSql, out dt);
                return dt;
            }
            catch
            {
                throw;
            }
        }
    }
}
